package rath.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 *
 * @author Jang-Ho Hwang, rath@xrath.com
 * @version 1.0, $Id$ since 2006/06/25
 */
public class Database {
	private static Map<String, GenericObjectPool> pools = new HashMap<String, GenericObjectPool>();
	private static Map<String, DataSource> sources = new HashMap<String, DataSource>();

	static {
		try {
			init();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void init() throws Exception {
		if (pools.size() > 0)
			return;

		Class.forName("com.mysql.jdbc.Driver");

		Properties prop = new Properties();
		prop.setProperty("user", "***********");
		prop.setProperty("password", "************");
		prop.setProperty("useUnicode", "true");
		prop.setProperty("characterEncoding", "utf-8");

		String uri = "jdbc:mysql://localhost:3306/****";

		GenericObjectPool pool = new GenericObjectPool(null);
		ConnectionFactory cf = new DriverManagerConnectionFactory(uri, prop);
		new PoolableConnectionFactory(cf, pool, null, null, false, true);

		DataSource source = new PoolingDataSource(pool);

		// install
		pools.put("******", pool);
		sources.put("********", source);

		pools = Collections.unmodifiableMap(pools);
		sources = Collections.unmodifiableMap(sources);
	}

	public static Connection get() throws SQLException {
		return sources.get("************").getConnection();
	}

	public static int doUpdate(String sql, Object... params) {
		int affected = 0;

		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = get();
			ps = con.prepareStatement(sql);
			int offset = 1;
			for (Object p : params) {
				if (p instanceof String)
					ps.setString(offset, (String) p);
				if (p instanceof Integer)
					ps.setInt(offset, ((Integer) p).intValue());
				if (p instanceof Float)
					ps.setDouble(offset, ((Float) p).floatValue());
				offset++;
			}
			affected = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			affected = -1;
		} finally {
			DbUtils.closeQuietly(con, ps, null);
		}
		return affected;
	}

	public static List<String> doQueryAll(String sql, Object... params) {
		List<String> list = new ArrayList<String>();
		String ret = null;

		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = get();
			ps = con.prepareStatement(sql);
			int offset = 1;
			for (Object p : params) {
				if (p instanceof String)
					ps.setString(offset, (String) p);
				if (p instanceof Integer)
					ps.setInt(offset, ((Integer) p).intValue());
				if (p instanceof Float)
					ps.setDouble(offset, ((Float) p).floatValue());
				offset++;
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(con, ps, rs);
		}
		return list;
	}

	public static int doQueryInt(String sql, Object... params) {
		String ret = doQuery(sql, params);
		if (ret == null)
			return -1;
		return Integer.parseInt(ret);
	}

	public static String doQuery(String sql, Object... params) {
		String ret = null;

		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = get();
			ps = con.prepareStatement(sql);
			int offset = 1;
			for (Object p : params) {
				if (p instanceof String)
					ps.setString(offset, (String) p);
				if (p instanceof Integer)
					ps.setInt(offset, ((Integer) p).intValue());
				if (p instanceof Float)
					ps.setDouble(offset, ((Float) p).floatValue());
				offset++;
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				ret = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(con, ps, rs);
		}
		return ret;
	}
}
